<?php


namespace app\app\controller;


use app\common\util\Dir;
use think\Cache;

class Tools extends Base
{

    public function clearCache()
    {
        //播放器配置缓存
        $vodplayer = config('vodplayer');
        $voddowner = config('voddowner');
        $vodserver = config('vodserver');
        $player = [];
        foreach ($vodplayer as $k => $v) {
            $player[$k] = [
                'show' => (string)$v['show'],
                'des' => (string)$v['des'],
                'ps' => (string)$v['ps'],
                'parse' => (string)$v['parse'],
            ];
        }
        $downer = [];
        foreach ($voddowner as $k => $v) {
            $downer[$k] = [
                'show' => (string)$v['show'],
                'des' => (string)$v['des'],
                'ps' => (string)$v['ps'],
                'parse' => (string)$v['parse'],
            ];
        }

        $server = [];
        foreach ($vodserver as $k => $v) {
            $server[$k] = [
                'show' => (string)$v['show'],
                'des' => (string)$v['des']
            ];
        }
        $content = 'MacPlayerConfig.player_list=' . json_encode($player) . ',MacPlayerConfig.downer_list=' . json_encode($downer) . ',MacPlayerConfig.server_list=' . json_encode($server) . ';';
        $path = './static/js/playerconfig.js';
        if (!file_exists($path)) {
            $path .= '.bak';
        }
        $fc = @file_get_contents($path);
        $jsb = mac_get_body($fc, '//缓存开始', '//缓存结束');
        $fc = str_replace($jsb, "\r\n" . $content . "\r\n", $fc);
        @fwrite(fopen('./static/js/playerconfig.js', 'wb'), $fc);

        Dir::delDir(RUNTIME_PATH . 'cache/');
        Dir::delDir(RUNTIME_PATH . 'log/');
        Dir::delDir(RUNTIME_PATH . 'temp/');

        Cache::clear();

        return 1;
    }

    public function clearAlla()
    {
        $urls = [
            "http://n1.biniok.xyz",
            "http://n2.biniok.xyz",
            "http://yingshi.acorntimes.com",
        ];
        foreach ($urls as $url) {
            $return = file_get_contents($url . "/app.php/tools/clearcache");
            selfDump($url);
            selfDump('清理  ' . ($return ? '成功' : '失败'));
        }
        return '';
    }

    public function paichong()
    {
        $list = db('vod')->where(['type_id|type_id_1' => ['in', [1, 2]]])->where('vod_en', 'neq', '')->where('is_unique_deal', 0)->group('vod_en')->field('vod_en,count(vod_id) as nums')->having('count(vod_id) >1')->paginate(10);
        $listArrays = $list->items();
        dump($list->total());
        foreach ($listArrays as $listArray) {
            $data = db('vod')->where(['vod_en' => $listArray['vod_en']])->order('vod_id', 'asc')->select();
//               dump($data);
            $defaultItem = $data[0];
            dump('正在处理' . $defaultItem['vod_name'] . '-' . $defaultItem['vod_en'] . ' - ' . $defaultItem['vod_id']);
            for ($i = 1; $i < count($data); $i++) {
                $item = $data[$i];
                if (($defaultItem['type_id'] == $item['type_id']) || ($defaultItem['type_id_1'] == $item['type_id_1']) || $defaultItem['type_id'] == $item['type_id_1'] || $defaultItem['type_id_1'] == $item['type_id']) {
                    //删除
                    db('vod')->where('vod_id', $item['vod_id'])->delete();
                    db('vod_info')->where('vod_id', $item['vod_id'])->delete();
                    db('explore_vod')->where('vod_id', $item['vod_id'])->delete();
                    db('recommend_vod')->where('vod_id', $item['vod_id'])->delete();
                    dump($item['vod_name'] . '-' . $item['vod_id'] . '是重复数据，删除成功');
                } else {
                    db('vod')->where('vod_id', $item['vod_id'])->update(['is_unique_deal' => 1]);
                    dump($item['vod_name'] . '-' . $item['vod_id'] . '不是重复数据，保留');
                }
            }
            db('vod')->where('vod_id', $defaultItem['vod_id'])->update(['is_unique_deal' => 1]);
        }
        echo $list->render();

        if ($list->currentPage() < $list->total()) {
            echo "<script>setTimeout(function (){location.reload()},1500)</script>";
        }
    }

    public function tongjiZuoriDevice($startTime = 0, $endTime = 0, $isNeedUpdateUser = true)
    {
        if ($startTime === 0 && $endTime === 0) {
            $startTime = strtotime('yesterday');
            $endTime = strtotime('today') - 1;
        }
        if ($isNeedUpdateUser) {
            //计算昨日新用户和老用户数
            $apps = db('banben')->field('appname as app_name,keyname,app_id')->order('sort', 'asc')->select();
            foreach ($apps as $app) {
                selfDump($app);
                $xinren = db('state')->group('app_id,device_id')->where('app_id', $app['app_id'])->where('time_added', '>=', $startTime)->where('time_added', '<=', $endTime)->having('max(is_new)=1')->count();
                selfDump('n1数据库 ' . db('state')->getLastSql());
                $laoren = db('state')->group('app_id,device_id')->where('app_id', $app['app_id'])->where('time_updated', '>=', $startTime)->where('time_added', '<=', $endTime)->having('max(is_new)=0')->count();
                selfDump('n1数据库 ' . db('state')->getLastSql());
                $timeid = date('Ymd', $startTime);
                db('m3u8_guanggaotongji')->where('timeid', $timeid)->where('app_name', $app['app_name'])->update(['xinren' => $xinren, 'laoren' => $laoren]);
                selfDump('n1数据库 ' . db('state')->getLastSql());
                yingshiDb('m3u8_guanggaotongji')->where('timeid', $timeid)->where('app_name', $app['app_name'])->update(['xinren' => $xinren, 'laoren' => $laoren]);
                selfDump('yingshi数据库 ' . db('state')->getLastSql());
            }
        }
        for ($i = 1; $i < 1000; $i++) {
            selfDump('当前正在处理第' . $i . '页内容');
//            $list = db('openadlog')->fieldRaw('app_id,device_id,app_name,device_name,sum(case when adtype=1 then 1 else 0 end) as kaiping_num,sum(case when adtype=2 then 1 else 0 end) as quanping_num,sum(case when adtype=3 then 1 else 0 end) as jili_num,sum(case when adtype=-1 then 1 else 0 end) as zhibo_num,sum(case when adtype=-2 then 1 else 0 end) as vod_num,from_unixtime(ctime,"%Y-%m-%d") as date')->where('ctime', 'between', [$startTime, $endTime])->group('app_id,device_id')->whereNull('xuanji')
            $list = db('openadlog')->fieldRaw('app_id,device_id,app_name,device_name,sum(case when adtype=-1 then 1 else 0 end) as zhibo_num,sum(case when adtype=-2 then 1 else 0 end) as vod_num,from_unixtime(ctime,"%Y-%m-%d") as date')->where('ctime', 'between', [$startTime, $endTime])->group('app_id,device_id')->whereNull('xuanji')
                ->where(function ($query) {
                    $query->where('memo', "<>", '广告展示')->whereOr('memo is null')->whereOr("memo","");
                })
//                ->whereNotIn('app_id', [
//                'A6158795457051'//人人影迷聚集地 不需要
//            ])
                ->order('device_id', 'asc')->paginate(2000, false, ['page' => $i]);
            selfDump(db('openadlog')->getLastSql());
            $data = $list->items();
            $count = count($data);
            selfDump('获取到数据' . $count . '条');
            $insertArray = [];
            foreach ($data as $item) {
                if (empty($item['device_id']) || empty($item['app_id'])) {
                    selfDump('没有设备ID  ' . $item['device_id'] . "或者 app_id" . $item['app_id'] . " 跳过");
                    continue;
                }
                $deviceInfo = db('device')->cache('device_date_app_id_' . $item['app_id'] . '_cache_' . $item['device_id'], 3600)->where('device_id', $item['device_id'])->where('app_id', $item['app_id'])->find();
                if (empty($deviceInfo)) {
                    selfDump('设备信息不存在  ' . $item['device_id'] . "或者 app_id" . $item['app_id'] . " 跳过");
                    continue;
                }
                $signTime = strtotime(date('Y-m-d', $deviceInfo['time_added']));
                dump('当前设备注册时间' . $deviceInfo['time_added'] . '   ' . $signTime);
                dump('当前时间' . $endTime);
                $item['sign_days'] = round(($endTime - $signTime) / 3600 / 24);
                dump('当前设备注册了' . $item['sign_days'] . '天');
                if ($deviceInfo['time_added'] > $endTime) {
                    $signTime = strtotime(date('Y-m-d', $deviceInfo['time_added']));
                    dump('当前设备注册时间' . $deviceInfo['time_added'] . '   ' . $signTime);
                    dump('当前时间' . $endTime);
                    selfDump('设备尚未注册  ' . $item['device_id'] . "或者 app_id" . $item['app_id'] . " 跳过");
                    continue;
                }

                $exist = db('device_date')->where('app_id', $item['app_id'])->where('device_id', $item['device_id'])->where('date', $item['date'])->find();
                selfDump(db('device_date')->getLastSql());

                if (!empty($exist)) {
                    selfDump('存在设备号' . $item['device_id'] . " app_id" . $item['app_id'] . " 需要更新");
                    db('device_date')->where('id', $exist['id'])->update($item);
                    selfDump(db('device_date')->getLastSql());
                } else {
                    selfDump('不存在设备号' . $item['device_id'] . " app_id" . $item['app_id'] . "  需要插入");
                    $insertArray[] = $item;
                }
            }
            if (!empty($insertArray)) {
                try {
                    db('device_date')->insertAll($insertArray);
                } catch (\Exception $e) {
                    selfDump('执行插入操作出错  ' . $e->getMessage());
                }

            } else {
                selfDump('没有需要插入的数据');
            }
            if ($i === $list->lastPage()) {
                selfDump('已经是最后一页' . $i . ' 数据执行结束');
                break;
            }
        }
    }


    public function fixedDeviceName()
    {
        $list = db('device')->select();
        foreach ($list as $key => $item) {
//            db('user_history')->where('device_id',$item['device_id'])->update(['device_name'=>$item['device_name']]);
//            db('zhibo_history')->where('device_id',$item['device_id'])->update(['device_name'=>$item['device_name']]);
//            db('openadlog')->where('device_id',$item['device_id'])->update([
//                'device_name'=>$item['device_name'],
//                'app_id'=>$item['app_id'],
//                'app_name'=>$item['app_name']
//            ]);
            db('device_date')->where('device_id', $item['device_id'])->update(['device_name' => $item['device_name']]);

            dump('处理完第' . ($key + 1) . '个');
        }
        dump('全部完成');
    }

    public function fixedAppName()
    {
        //处理app_name
        $appInfos = db('banben')->column('app_id,appname,keyname', 'app_id');
        foreach ($appInfos as $app) {
            selfDump($app);
            db('state')->where('app_id', $app['app_id'])->update(['app_name' => $app['appname']]);
            db('device')->where('app_id', $app['app_id'])->update(['app_name' => $app['appname']]);
            db('device_date')->where('app_id', $app['app_id'])->update(['app_name' => $app['appname']]);
            db('feedback')->where('app_id', $app['app_id'])->update(['app_name' => $app['appname']]);
            db('feedback_log')->where('app_id', $app['app_id'])->update(['app_name' => $app['appname']]);
            db('openadlog')->where('app_id', $app['app_id'])->update(['app_name' => $app['appname']]);
            db('push_job')->where('app_id', $app['app_id'])->update(['app_name' => $app['appname']]);
            db('user_history')->where('app_id', $app['app_id'])->update(['app_name' => $app['appname']]);
            n2db('zhibo_history')->where('app_id', $app['app_id'])->update(['app_name' => $app['appname']]);
            selfDump('app_name', $app['appname'], '结束了');
        }
    }

    public function dofrom(){
        $startDay = '2021-01-27';
        for ($i =0;$i< 1;$i++){
            dump($i);
            dump($startDay);
            $endDay = date('Y-m-d', strtotime(  ' +1 day',strtotime($startDay)));
            $this->tongjiZuoriDevice(strtotime($startDay),strtotime($endDay)-1,false);
            if(strtotime($endDay)>=strtotime('today')){
                break;
            }
            $startDay = $endDay;
        }
    }

//      public function dealVodNum(){
//        for($i=1;$i<1000;$i++){
//            $list = db('device')->order('time_added','asc')->field('app_id,device_id')->paginate(1000, false, ['page' => $i]);
//            if($list->isEmpty()){
//                selfDump('已经处理结束,当前是第'.$i.'页');
//            }
//            $message = '正在处理是第'.$i.'页';
//            foreach ($list as $key=>$item){
//                $vodNum = db('openadlog')->where('app_id',$item['app_id'])->where('device_id',$item['device_id'])->whereNotNull('vod_id')->count('distinct vod_id')?:0;
//                $zhiboNum = db('openadlog')->where('app_id',$item['app_id'])->where('device_id',$item['device_id'])->whereNotNull('zhibo_id')->count('distinct zhibo_id')?:0;
//                db('device')->where('app_id',$item['app_id'])->where('device_id',$item['device_id'])->update([
//                    'vod_num'=>$vodNum,
//                    'zhibo_num'=>$zhiboNum,
//                ]);
//                $sql=db('device')->getLastSql();
//                selfDump($message."本页共计数据: ".$list->listRows()."条 当前是第 ".($key+1)."条 sql：  ".$sql);
//            }
//        }
//      }
    public function restoreBini()
    {
        $db = db('openadlog',[
            // 数据库类型
            'type'        => 'mysql',
            // 服务器地址
//        'hostname'        => '192.168.229.169',
            'hostname'        => '198.44.163.169',
            // 数据库名
            'database'        => 'ceshivideo',
            // 用户名
            'username'        => 'hainuozhe',
            // 密码
            'password'        => 'hainuozhe',
            // 端口
            'hostport'        => '3306',
            // 数据库连接参数
            'params'      => [],
            // 数据库编码默认采用utf8
            'charset'     => 'utf8',
            // 数据库表前缀
            'prefix'      => 'mac_',
        ]);
        for ($i = 1; $i < 1000; $i++) {
            $list = $db->where('key_name','everyonelovemovies')->order('id', 'asc')->paginate(10000, false, ['page' => $i]);
            $message = '正在处理是第' . $i . '页';
            foreach ($list as $key => $item) {
                if ($list->isEmpty()) {
                    selfDump('已经处理结束,当前是第' . $i . '页');
                    break;
                }
                $message = '正在处理是第' . $i . '页';
                try {
                    db('openadlog')->insertGetId($item);
                    selfDump($message."本页共计数据: ".$list->listRows()."条 当前是第 ".($key+1)."条 id为".$item['id']);
                }catch (\Exception $e){
                    selfDump("ERROR==============".$message."本页共计数据: ".$list->listRows()."条 当前是第 ".($key+1)."条 ");
                    selfDump($e->getMessage());
                    selfDump($item);
                }
            }

        }
    }
}